package com.wangc.report.utils;

import com.wangc.report.dao.ConfigMapper;
import com.wangc.report.entity.ConfigEntity;
import com.wangc.report.entity.DateEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class ReportCheck {
    private static final Logger LOG = LoggerFactory.getLogger(ReportCheck.class);
    public  Connection con = null;
    private final String OLD = "_old";
    private final String NEW = "_new";
    private final int batchCommitNum = 10000;
    private final String CheckStateTure = "true";
    private final String CheckStateFalse = "false";
    private final int RowWeighting = 1000;
    private final int LogBase = 10000;
    @Autowired
    private DataSource dataSource;
    @Resource
    private ConfigMapper configMapper;

    /**
     * 获取报表文件配置信息
     *
     * @param table
     * @return
     */
    public ConfigEntity getConfigByTable(String table) {
        return configMapper.selectByTableId(table);
    }

    /**
     * 获取报表表结构信息
     *
     * @param table
     * @return
     */
    public List<String> getTableInfoByTable(String table) {
        return configMapper.selectColunmByTable(table);
    }
    /**
     * 比对规则
     * 1.以老数据为基准
     */
    /**
     * 从文件中导入报表
     *
     * @param table
     * @param dataDt
     * @param newOldFlg
     * @throws Exception
     */
    public void loadSourceFile(String table, String dataDt, String newOldFlg) throws Exception {
        LOG.info("loadSourceFile start");
        String fileName = table + "" + (newOldFlg == "0" ? OLD : NEW) + ".txt";
        String filePath = getConfigByTable(table).getFilePath();
        LOG.debug("load file {}", filePath + fileName);
        Connection con = getConnection();
        con.setAutoCommit(false);
        Statement stmt = con.createStatement();
        String sql = "";
        //删除存档数据
        sql = "delete  from report_source_table where table_id= '" + table + "' and new_old_flag= '" + newOldFlg + "' and data_dt= '" + dataDt + "'";
        LOG.debug("sql {}", sql);
        stmt.execute(sql);
        //数据插入准备
        sql = "INSERT INTO `report_source_table`(`table_id`, `new_old_flag`, `data_dt`, `col_seq_no`, `row_seq_no`, `report_data`, `update_time`, `state`) VALUES (?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1, table);
        pstmt.setString(2, newOldFlg);
        pstmt.setString(3, dataDt);
        pstmt.setString(7, null);
        pstmt.setString(8, "0");
        //文件读取
        BufferedReader br = new BufferedReader(new FileReader(filePath + fileName));
        String s = null;
        String[] lineList = null;
        int i = 0;
        while ((s = br.readLine()) != null) {
            i++;
            lineList = s.split("\\|");
            for (int j = 1; j <= lineList.length; j++) {
                pstmt.setInt(4, j);
                pstmt.setInt(5, i);
                pstmt.setString(6, lineList[j - 1]);
                pstmt.addBatch();
            }
            if (i % batchCommitNum == 0) {
                pstmt.executeBatch();
                con.commit();
                LOG.debug("commit  {}", i);
            }
        }
        if (i % batchCommitNum != 0) {
            pstmt.executeBatch();
            con.commit();
            LOG.debug("commit  {}", i);
        }
        br.close();
        pstmt.close();
        stmt.close();
        con.close();
        LOG.info("loadSourceFile over");
    }

    /**
     * 报表数据导入源表
     *
     * @param dataDt
     */
    public void loadSourceTable(String table, String dataDt, String newOldFlg) throws Exception {
        LOG.info("loadSourceTable start");
        String tableName = table + "" + (newOldFlg == "0" ? OLD : NEW);
        LOG.debug("load tableName {}", tableName);
        Statement stmt = null;
        Connection con = getConnection();
        con.setAutoCommit(false);
        stmt = con.createStatement();
        ResultSet rset = null;
        String sql = "";
        //获取排序
        String orderString = "";
        String[] indexList = getIndex(table);
        for (String index : indexList) {
            if (null != index) orderString += "," + index;
        }
        orderString = orderString.substring(1);
        LOG.debug("orderString= {}", orderString);
        //删除存档数据
        sql = "delete  from report_source_table where table_id= '" + table + "' and new_old_flag= '" + newOldFlg + "' and data_dt= '" + dataDt + "'";
        LOG.debug("sql {}", sql);
        stmt.execute(sql);
        //获取报表数据导入源表
        sql = "select * from " + tableName + " order by " + orderString;
        LOG.debug("sql {}", sql);
        rset = stmt.executeQuery(sql);
        sql = "INSERT INTO `report_source_table`(`table_id`, `new_old_flag`, `data_dt`, `col_seq_no`, `row_seq_no`, `report_data`, `update_time`, `state`) VALUES (?,?,?,?,?,?,?,?);";
        PreparedStatement pstmt = con.prepareStatement(sql);
        int columnCount = rset.getMetaData().getColumnCount();
        int i = 0;
        while (rset.next()) {
            LOG.debug("{}", ++i);
            for (int j = 1; j <= columnCount; j++) {
                pstmt.setString(1, table);
                pstmt.setString(2, newOldFlg);
                pstmt.setString(3, dataDt);
                pstmt.setString(4, String.valueOf(j));
                pstmt.setString(5, String.valueOf(i));
                pstmt.setString(6, rset.getObject(j) != null ? rset.getObject(j).toString() : "");
                pstmt.setString(7, null);
                pstmt.setString(8, "0");
                pstmt.addBatch();
            }
            if (i % batchCommitNum == 0) {
                pstmt.executeBatch();
                con.commit();
            }
        }
        if (i % batchCommitNum != 0) {
            pstmt.executeBatch();
            con.commit();
        }
        pstmt.close();
        stmt.close();
        con.close();
        LOG.info("loadSourceTable over");
    }

    /**
     * 报表生成
     *
     * @param table
     * @param dataDt
     */
    public void generateReport(String table, String dataDt) throws Exception {
        LOG.info("generateReport start");
        int[] indexList = getIndexSeq(table);
        String sql = "";
        Statement stmt0 = null;
        PreparedStatement stmt1 = null;
        PreparedStatement stmtInsert = null;
        ResultSet rset0 = null;
        ResultSet rset1 = null;

        //获取连接
        Connection con = getConnection();
        con.setAutoCommit(false);

        stmt0 = con.createStatement();
        //删除可能重复的数据
        stmt0.execute("delete  from report_traget_table where table_id= '" + table + "' and data_dt= '" + dataDt + "'");
        //提交清除
        con.commit();
        //老数据集
        sql = getSql(table, dataDt, indexList, "0");
        rset0 = stmt0.executeQuery(sql);
        //新数据集
        sql = getSql(table, dataDt, indexList, "1");
        stmt1 = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        rset1 = stmt1.executeQuery();
        Map<Integer, Integer> oldNewMapping = new HashMap<>();
        LOG.debug("映射 {}", System.currentTimeMillis());
        int columnCount = rset0.getMetaData().getColumnCount();
        int p = 0;
        int columnCount1 = rset1.getMetaData().getColumnCount();
        String st = "";
        int d = 0;
        Map rowData = new HashMap();
        LOG.debug("新数据索引存入map，映射准备：start {}", System.currentTimeMillis());
        while (rset1.next()) {
            d++;
            if (d % LogBase == 0) LOG.debug("{}", d);
            st = "";
            for (int i = 2; i <= columnCount1; i++) {
                st = st + (rset1.getObject(i) != null ? rset1.getObject(i).toString() : "");
            }
            rowData.put(st, rset1.getObject(1));
        }
        LOG.debug("新数据索引存入map，映射准备：over {}", System.currentTimeMillis());

        LOG.debug("新旧映射 start  {}", System.currentTimeMillis());
        while (rset0.next()) {
            p++;
            if (p % LogBase == 0) LOG.debug("{}", p);
            st = "";
            for (int i = 2; i <= columnCount; i++) {
                st = st + (rset0.getObject(i) != null ? rset0.getObject(i).toString() : "");
            }
            int index = (Integer) rowData.get(st);
            oldNewMapping.put(rset0.getInt(1), index);
        }
        LOG.debug("新旧映射 over {}", System.currentTimeMillis());

        //插入
        sql = "INSERT INTO `report_traget_table`(`table_id`, `data_dt`, `row_no`, `col_seq_no`, `old_data`, `new_data`, `check_result`, `update_time`, `state`) VALUES(?,?,?,?,?,?,?,?,?)";
        stmtInsert = con.prepareStatement(sql);
        stmtInsert.setString(1, table);
        stmtInsert.setString(2, dataDt);
        stmtInsert.setString(8, null);
        stmtInsert.setString(9, "0");
        //根据映射关系插入数据
        //老数据集
        sql = "SELECT a.row_seq_no,a.col_seq_no,a.report_data FROM report_source_table a where  a.table_id='" + table + "'  and a.new_old_flag=0 and a.data_dt='" + dataDt + "' order by a.row_seq_no ,a.col_seq_no";
        rset0 = stmt0.executeQuery(sql);
        //新数据集
        sql = "SELECT a.row_seq_no,a.col_seq_no,a.report_data FROM report_source_table a where  a.table_id='" + table + "'  and a.new_old_flag=1 and a.data_dt='" + dataDt + "' order by a.row_seq_no,a.col_seq_no";
        rset1 = stmt1.executeQuery(sql);
        Map<Integer, DateEntity> map = new HashMap<>();

        LOG.debug("存储新数据结果集 start {}", System.currentTimeMillis());
        while (rset1.next()) {
            DateEntity dateEntity = new DateEntity();
            dateEntity.setRowSeqNo(rset1.getInt(1));
            dateEntity.setColSeqNo(rset1.getInt(2));
            dateEntity.setReportData(rset1.getString(3));
            map.put(rset1.getInt(1) * RowWeighting + rset1.getInt(2), dateEntity);
        }
        LOG.debug("存储新数据结果集 over {}", System.currentTimeMillis());

        LOG.debug("核对插入 start  {}", System.currentTimeMillis());
        //获取描述
        String[] describe = getDescribe(table);
        int k = 0;
        String newData = "";
        while (rset0.next()) {
            k++;
            //标题行
            if (rset0.getInt(1) == 1) {
                stmtInsert.setInt(3, 0);
                stmtInsert.setInt(4, rset0.getInt(2));
                stmtInsert.setString(5, describe[rset0.getInt(2) - 1]);
                stmtInsert.setString(6, "");
                stmtInsert.setString(7, "");
                stmtInsert.addBatch();
                stmtInsert.setInt(3, 1);
                stmtInsert.setInt(4, rset0.getInt(2));
                stmtInsert.setString(5, "老数据");
                stmtInsert.setString(6, "新数据");
                stmtInsert.setString(7, "核对状态");
                stmtInsert.addBatch();
            }
            //核对行
            //添加标题行后递增1
            stmtInsert.setInt(3, rset0.getInt(1) + 1);
            //列数
            stmtInsert.setInt(4, rset0.getInt(2));
            //旧报表源数据
            stmtInsert.setString(5, rset0.getString(3));
            //新数据行号 写错了
            int oldRowNo = oldNewMapping.get(rset0.getInt(1));

            int indexNo = oldRowNo * RowWeighting + rset0.getInt(2);
            if (null == map.get(indexNo)) {
                LOG.debug("{},{}跳过！", k, indexNo);
                continue;
            }
            newData = "";
            newData = map.get(indexNo).getReportData();
            stmtInsert.setString(6, newData);
            //核对
            String checkState = CheckStateFalse;
            //映射处理---添加
            if (rset0.getString(3).equals(newData)) checkState = CheckStateTure;
            stmtInsert.setString(7, checkState);
            stmtInsert.addBatch();

            if (k % batchCommitNum == 0) {
                stmtInsert.executeBatch();
                stmtInsert.clearBatch();
                con.commit();
                LOG.debug("insert {}", k);
            }
        }
        LOG.debug("核对插入 over  {}", System.currentTimeMillis());
        if (k % batchCommitNum != 0) {
            stmtInsert.executeBatch();
            stmtInsert.clearBatch();
            con.commit();
            LOG.debug("commit {}", k);
        }
        LOG.debug("核对插入 over2  {}", System.currentTimeMillis());
        //缓存释放
        oldNewMapping.clear();
        map.clear();
        rowData.clear();
        //连接释放
        stmtInsert.close();
        rset1.close();
        rset0.close();
        stmt0.close();
        stmt1.close();
        con.close();
        LOG.info("generateReport over");
    }

    /**
     * 生成sql 暂不处理排序为空的
     *
     * @param table
     * @param dataDt
     * @param indexList
     * @return
     */
    public String getSql(String table, String dataDt, int[] indexList, String newOldFlg) {
        String alias = "bcdefghijklmnopqrstuvwxyz";
        int length = 0;
        for (int i = 0; i < indexList.length; i++) {
            if (0 == indexList[i]) break;
            length++;
        }
        String sql1 = "a.row_seq_no,a.report_data";
        String sql2 = "report_source_table a ";
        String sql3 = "a.table_id = '" + table + "' AND a.new_old_flag = '" + newOldFlg + "' AND a.data_dt = '" + dataDt + "' AND a.col_seq_no = " + indexList[0];
        String sql4 = "a.report_data";

        for (int i = 1; i < length; i++) {
            sql1 += "," + alias.substring(i - 1, i) + ".report_data";
            sql2 += "LEFT JOIN report_source_table " + alias.substring(i - 1, i) + " ON a.row_seq_no = " + alias.substring(i - 1, i) + ".row_seq_no";
            sql3 += " AND " + alias.substring(i - 1, i) + ".table_id = '" + table + "' AND " + alias.substring(i - 1, i) + ".new_old_flag = '" + newOldFlg + "' AND " + alias.substring(i - 1, i) + ".data_dt = '" + dataDt + "' AND " + alias.substring(i - 1, i) + ".col_seq_no = " + indexList[i];
            sql4 += "," + alias.substring(i - 1, i) + ".report_data";
        }
        //String sql="select "+sql1+" from "+sql2+" where "+sql3+" order by " +sql4;
        String sql = "select " + sql1 + " from " + sql2 + " where " + sql3;
        LOG.debug(sql);
        return sql;
    }

    /**
     * 获取排序字段/索引
     *
     * @param table
     * @return 对应列字段名
     */
    public String[] getIndex(String table) {
        String[] indexList = new String[30];
        ConfigEntity configEntity = getConfigByTable(table);
        String[] fieldDescribeList = configEntity.getFieldDescribe().split("\\|");
        String[] sortRuleList = configEntity.getSortRule().split("\\|");
        List<String> feildList = getTableInfoByTable(table + OLD);
        int i = 0;
        String feild = "";
        for (String sortRule : sortRuleList) {
            for (int j = 0; j < fieldDescribeList.length; j++) {
                if (sortRule.equals(fieldDescribeList[j])) {
                    feild = feildList.get(j);
                }
            }
            indexList[i++] = feild;
        }
        i = 0;
        for (String index : indexList) {
            if (null != index) LOG.debug("索引字段：{}-{}", ++i, index);
        }
        return indexList;
    }

    /**
     * 获取排序字段/索引
     *
     * @param table
     * @return 索引列号
     */
    public int[] getIndexSeq(String table) {
        int[] indexList = new int[30];
        ConfigEntity configEntity = getConfigByTable(table);
        String[] fieldDescribeList = configEntity.getFieldDescribe().split("\\|");
        String[] sortRuleList = configEntity.getSortRule().split("\\|");
        int i = 0;
        int seq = 0;
        for (String sortRule : sortRuleList) {
            for (int j = 0; j < fieldDescribeList.length; j++) {
                if (sortRule.equals(fieldDescribeList[j])) {
                    seq = j + 1;
                }
            }
            indexList[i++] = seq;
        }
        i = 0;
        for (int index : indexList) {
            if (0 != index) LOG.debug("索引列号：{}-{}", ++i, index);
        }
        return indexList;
    }

    /**
     * 获取列名
     *
     * @param table
     * @returnD
     */
    public String[] getDescribe(String table) {
        ConfigEntity configEntity = getConfigByTable(table);
        String[] fieldDescribeList = configEntity.getFieldDescribe().split("\\|");
        int i = 0;
        for (String field : fieldDescribeList) {
            LOG.debug("表字段描述：{}-{}", ++i, field);
        }
        return fieldDescribeList;
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public Connection getConnection() {
        try {
            con = dataSource.getConnection();
        } catch (SQLException e) {
            LOG.error(e.getMessage());
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return con;
    }
}
